# -*- coding: utf-8 -*-


import json
import threading
from contextlib import contextmanager
from datetime import timedelta
from typing import Dict, List, Any, Optional

import mysql.connector
from mysql.connector import Error

from LogConfig import logger

# 配置日志


# MySQL数据库配置
DB_CONFIG = {
    "host": "rm-9yx364w4ey002550o.mysql.rds.ops.lysjfw.chnenergy.com.cn",
    "port": 3306,
    "user": "admin01",
    "password": "Clypglj!#%246",
    "database": "OCR_CER",
    "charset": "utf8mb4"
}

# 线程锁，用于确保线程安全
_db_lock = threading.Lock()

# 标记数据库是否已初始化
_db_initialized = False

# 转换时间格式
from datetime import datetime


def convert_to_isoformat(time_value):
    """将时间值转换为ISO格式字符串"""
    if time_value is None:
        return None
    if isinstance(time_value, datetime):
        return time_value.isoformat()
    # 如果已经是字符串，直接返回
    if isinstance(time_value, str):
        return time_value
    # 其他类型尝试转换为datetime再转换为ISO格式
    try:
        return datetime.fromisoformat(str(time_value))
    except (ValueError, TypeError):
        return str(time_value)  # 最后的备选方案





@contextmanager
def _get_connection():
    """
    获取数据库连接的上下文管理器
    """
    conn = None
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        yield conn
    except Error as e:
        logger.error(f"数据库连接失败: {e}")
        raise
    finally:
        if conn and conn.is_connected():
            conn.close()


def init_database():
    """
    初始化数据库表结构
    """
    global _db_initialized

    if _db_initialized:
        return

    try:
        with _get_connection() as conn:
            cursor = conn.cursor()

            # 创建校验结果表（如果不存在）
            cursor.execute('''
                           CREATE TABLE IF NOT EXISTS validation_result
                           (
                               id
                               BIGINT
                               PRIMARY
                               KEY
                               AUTO_INCREMENT,
                               user_id
                               VARCHAR
                           (
                               50
                           ) NOT NULL COMMENT '用户ID',
                               code VARCHAR
                           (
                               50
                           ) NOT NULL COMMENT '单据号',
                               if_pass BOOLEAN NOT NULL COMMENT '是否通过：true-通过，false-未通过',
                               val_result JSON NOT NULL COMMENT '校验概览结果（JSON格式）',
                               error_msg JSON COMMENT '详细错误信息（JSON格式）',
                               push_time DATETIME NOT NULL COMMENT '推送时间',
                               created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                               updated_time DATETIME DEFAULT CURRENT_TIMESTAMP
                               ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                               UNIQUE KEY uk_code
                           (
                               code
                           ),
                               INDEX idx_push_time
                           (
                               push_time
                           ),
                               INDEX idx_user_time
                           (
                               user_id,
                               push_time
                           )
                               ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='OCR制证校验结果表'
                           ''')

            conn.commit()
            _db_initialized = True
            logger.info("数据库表结构初始化完成")

    except Error as e:
        logger.error(f"初始化数据库失败: {e}")
        raise


#         save_validation_result_to_mysql(用户名, 单据号, if_pass, validation_data, detailed_errors,
#                                                            datetime.now().strftime('%Y-%m-%d %H:%M:%S'))

def save_validation_result_to_mysql(user_id: str, code: str, if_pass: bool, val_result: Any,
                                    error_msg: Any = None,
                                    push_time: Any = None) -> bool:
    """
    保存校验结果到MySQL数据库

    Args:
        user_id: 用户ID
        code: 单据号
        if_pass: 通过标识（boolean）
        val_result: 校验结果（dict）
        error_msg: 详细错误信息（可为list/dict/str/None）
        push_time: 推送时间（datetime/ISO字符串/None），默认为当前时间

    Returns:
        bool: 保存是否成功
    """
    # 确保数据库已初始化
    init_database()

    try:
        # 归一化 if_pass 到布尔
        try:
            if isinstance(if_pass, (bool, int)):
                if_pass = bool(if_pass)
            elif isinstance(if_pass, str):
                i_p = if_pass.strip().lower()
                if_pass = i_p in ("true", "1", "yes", "y", "t")
            else:
                if_pass = bool(if_pass)
        except Exception as e:
            logger.warning(f"无法解析if_pass为布尔值，已按False处理: {e}")
            if_pass = False

        # 归一化 error_msg 为list
        if error_msg is None:
            normalized_error_msg = []
        elif isinstance(error_msg, list):
            normalized_error_msg = error_msg
        elif isinstance(error_msg, dict):
            normalized_error_msg = [error_msg]
        else:
            normalized_error_msg = [str(error_msg)]

        with _db_lock:
            # 归一化 push_time
            if push_time is None:
                push_time_dt = datetime.now()
            elif isinstance(push_time, datetime):
                push_time_dt = push_time
            else:
                # 尝试解析时间字符串
                try:
                    push_time_dt = datetime.fromisoformat(str(push_time).replace('Z', '+00:00'))
                except ValueError:
                    push_time_dt = datetime.now()

            # 归一化 val_result 为JSON字符串
            if val_result is None:
                val_result_json = json.dumps({}, ensure_ascii=False)
            elif isinstance(val_result, str):
                # 如果已经是字符串，检查是否为有效JSON
                try:
                    json.loads(val_result)  # 验证是否为有效JSON
                    val_result_json = val_result
                except (json.JSONDecodeError, TypeError):
                    # 如果不是有效JSON，将其作为普通字符串包装
                    val_result_json = json.dumps({"result": val_result}, ensure_ascii=False)
            else:
                # 对于dict、list或其他类型，直接序列化
                val_result_json = json.dumps(val_result, ensure_ascii=False, default=str)

            with _get_connection() as conn:
                cursor = conn.cursor()

                # 使用INSERT ... ON DUPLICATE KEY UPDATE来处理重复数据
                # 当code相同时，更新现有记录
                sql = '''
                      INSERT INTO validation_result
                          (user_id, code, if_pass, val_result, error_msg, push_time)
                      VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY
                      UPDATE
                          user_id=
                      VALUES (user_id), if_pass=
                      VALUES (if_pass), val_result=
                      VALUES (val_result), error_msg=
                      VALUES (error_msg), push_time=
                      VALUES (push_time), updated_time = CURRENT_TIMESTAMP
                      '''

                # 转换错误信息为JSON字符串
                error_msg_json = json.dumps(normalized_error_msg, ensure_ascii=False, default=str)

                cursor.execute(sql, (user_id, code, if_pass, val_result_json, error_msg_json, push_time_dt))
                conn.commit()

                logger.info(f"成功保存校验结果: {code}，结果: {val_result}，推送时间: {push_time_dt}")
                return True

    except Error as e:
        logger.error(f"保存校验结果失败: {code} - {e}")
        return False
    except Exception as e:
        logger.error(f"保存校验结果异常: {code} - {e}")
        return False


def get_validation_result(user_id: str, document_number: str) -> Optional[Dict[str, Any]]:
    """
    获取指定单据的最新校验结果

    Args:
        user_id: 用户ID
        document_number: 单据号

    Returns:
        Dict: 校验结果数据，如果不存在则返回None
    """
    # 确保数据库已初始化
    init_database()

    try:
        with _get_connection() as conn:
            cursor = conn.cursor(dictionary=True)

            cursor.execute('''
                           SELECT *
                           FROM validation_result
                           WHERE user_id = %s
                             AND code = %s
                           ORDER BY push_time DESC LIMIT 1
                           ''', (user_id, document_number))

            row = cursor.fetchone()
            if row:
                return _row_to_dict(row)
            return None

    except Error as e:
        logger.error(f"获取校验结果失败: {document_number} - {e}")
        return None
    except Exception as e:
        logger.error(f"获取校验结果异常: {document_number} - {e}")
        return None


def get_all_validation_results(user_id: str, limit: int = 100, offset: int = 0) -> List[Dict[str, Any]]:
    """
    获取所有校验结果

    Args:
        user_id: 用户ID
        limit: 返回结果数量限制
        offset: 偏移量

    Returns:
        List: 校验结果列表
    """
    # 确保数据库已初始化
    init_database()

    try:
        with _get_connection() as conn:
            cursor = conn.cursor(dictionary=True)

            cursor.execute('''
                           SELECT *
                           FROM validation_result
                           WHERE user_id = %s
                           ORDER BY push_time DESC LIMIT %s
                           OFFSET %s
                           ''', (user_id, limit, offset))

            rows = cursor.fetchall()
            return [_row_to_dict(row) for row in rows]

    except Error as e:
        logger.error(f"获取所有校验结果失败: {e}")
        return []
    except Exception as e:
        logger.error(f"获取所有校验结果异常: {e}")
        return []


def get_validation_results_by_time_range(user_id: str, start_time: Any, end_time: Any) -> List[Dict[str, Any]]:
    """
    按时间范围获取校验结果

    Args:
        user_id: 用户ID
        start_time: 开始时间（datetime或ISO字符串）
        end_time: 结束时间（datetime或ISO字符串）

    Returns:
        List: 校验结果列表
    """
    # 确保数据库已初始化
    init_database()

    try:
        with _get_connection() as conn:
            cursor = conn.cursor(dictionary=True)

            cursor.execute('''
                           SELECT *
                           FROM validation_result
                           WHERE user_id = %s
                             AND push_time BETWEEN %s AND %s
                           ORDER BY push_time DESC
                           ''', (user_id, start_time, end_time))

            rows = cursor.fetchall()
            return [_row_to_dict(row) for row in rows]

    except Error as e:
        logger.error(f"按时间范围获取校验结果失败: {e}")
        return []
    except Exception as e:
        logger.error(f"按时间范围获取校验结果异常: {e}")
        return []


def delete_validation_result(user_id: str, document_number: str, push_time: str = None) -> bool:
    """
    删除校验结果

    Args:
        user_id: 用户ID
        document_number: 单据号
        push_time: 推送时间，如果不指定则删除该单据的所有结果

    Returns:
        bool: 删除是否成功
    """
    # 确保数据库已初始化
    init_database()

    try:
        with _db_lock:
            with _get_connection() as conn:
                cursor = conn.cursor()

                if push_time:
                    cursor.execute('''
                                   DELETE
                                   FROM validation_result
                                   WHERE user_id = %s
                                     AND code = %s
                                     AND push_time = %s
                                   ''', (user_id, document_number, push_time))
                else:
                    cursor.execute('''
                                   DELETE
                                   FROM validation_result
                                   WHERE user_id = %s
                                     AND code = %s
                                   ''', (user_id, document_number))

                conn.commit()

                logger.info(f"成功删除校验结果: {document_number}")
                return True

    except Error as e:
        logger.error(f"删除校验结果失败: {document_number} - {e}")
        return False
    except Exception as e:
        logger.error(f"删除校验结果异常: {document_number} - {e}")
        return False


def cleanup_old_results(user_id: str, days: int = 30) -> int:
    """
    清理旧的校验结果

    Args:
        user_id: 用户ID
        days: 保留天数，超过此天数的结果将被删除

    Returns:
        int: 清理的记录数量
    """
    # 确保数据库已初始化
    init_database()

    try:
        cutoff_time = datetime.now() - timedelta(days=days)

        with _db_lock:
            with _get_connection() as conn:
                cursor = conn.cursor()

                cursor.execute('''
                               DELETE
                               FROM validation_result
                               WHERE user_id = %s
                                 AND created_time < %s
                               ''', (user_id, cutoff_time))

                affected_rows = cursor.rowcount
                conn.commit()

                logger.info(f"清理了 {affected_rows} 条过期校验结果")
                return affected_rows

    except Error as e:
        logger.error(f"清理旧校验结果失败: {e}")
        return 0
    except Exception as e:
        logger.error(f"清理旧校验结果异常: {e}")
        return 0


def get_cache_stats(user_id: str) -> Dict[str, Any]:
    """
    获取缓存统计信息

    Args:
        user_id: 用户ID

    Returns:
        Dict: 统计信息
    """
    # 确保数据库已初始化
    init_database()

    try:
        with _get_connection() as conn:
            cursor = conn.cursor(dictionary=True)

            # 总记录数
            cursor.execute("SELECT COUNT(*) as count FROM validation_result WHERE user_id = %s", (user_id,))
            total_count = cursor.fetchone()['count']

            # 成功和失败统计
            cursor.execute(
                "SELECT COUNT(*) as count FROM validation_result WHERE user_id = %s AND if_pass = TRUE",
                (user_id,))
            success_count = cursor.fetchone()['count']

            cursor.execute(
                "SELECT COUNT(*) as count FROM validation_result WHERE user_id = %s AND if_pass = FALSE",
                (user_id,))
            failed_count = cursor.fetchone()['count']

            # 今日记录数
            today = datetime.now().date()
            cursor.execute('''
                           SELECT COUNT(*) as count
                           FROM validation_result
                           WHERE user_id = %s
                             AND DATE (created_time) = %s
                           ''', (user_id, today))
            today_count = cursor.fetchone()['count']

            # 最新记录时间
            cursor.execute('''
                           SELECT MAX(push_time) as latest_time
                           FROM validation_result
                           WHERE user_id = %s
                           ''', (user_id,))
            latest_result = cursor.fetchone()
            latest_record_time = latest_result['latest_time'] if latest_result['latest_time'] else 'N/A'

            return {
                'total_records': total_count,
                'success_records': success_count,
                'failed_records': failed_count,
                'today_records': today_count,
                'database_host': DB_CONFIG['host'],
                'database_name': DB_CONFIG['database'],
                'user_id': user_id,
                'latest_record_time': str(latest_record_time) if latest_record_time != 'N/A' else 'N/A'
            }

    except Error as e:
        logger.error(f"获取缓存统计失败: {e}")
        return {}
    except Exception as e:
        logger.error(f"获取缓存统计异常: {e}")
        return {}


def _row_to_dict(row) -> Dict[str, Any]:
    """
    将数据库行转换为字典格式

    Args:
        row: 数据库行数据（字典格式）

    Returns:
        Dict: 转换后的字典.
    """
    try:
        # MySQL connector返回的已经是字典格式
        result = dict(row)

        # 转换时间格式
        if 'created_time' in result:
            result['created_time'] = convert_to_isoformat(result['created_time'])
        if 'push_time' in result:
            result['push_time'] = convert_to_isoformat(result['push_time'])


        # 解析JSON字段
        if 'error_msg' in result and result['error_msg']:
            if isinstance(result['error_msg'], (dict, list)):
                pass  # 已经是解析后的结构
            else:
                try:
                    result['error_msg'] = json.loads(result['error_msg'])
                except Exception:
                    logger.warning(f"无法解析error_msg JSON: {result['error_msg']}")

        return result

    except Exception as e:
        logger.error(f"转换数据行失败: {e}")
        return {}
